



-- -----------------------------------------------------------
-- Entity Designer DDL Script for MySQL Server 4.1 and higher
-- -----------------------------------------------------------
-- Date Created: 04/24/2012 14:44:30
-- Generated from EDMX file: C:\Users\Andrew\Documents\Visual Studio 2010\Projects\Project2\KSUNO EMS\Models\EMSModel.edmx
-- Target version: 2.0.0.0
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- NOTE: if the constraint does not exist, an ignorable error will be reported.
-- --------------------------------------------------

--    ALTER TABLE `EmployeeAvailabilities` DROP CONSTRAINT `FK_EmployeeAvailability_EmployeeId`;
--    ALTER TABLE `EmployeeSchedules` DROP CONSTRAINT `FK_EmployeeSchedules_EmployeeId`;
--    ALTER TABLE `EmployeeTestResults` DROP CONSTRAINT `FK_EmployeeTestResults_EmplyeeId`;
--    ALTER TABLE `EmployeeTestResults` DROP CONSTRAINT `FK_EmployeeTestResults_AnswerId`;
--    ALTER TABLE `EmployeeTestResults` DROP CONSTRAINT `FK_EmployeeTestResults_QuestionId`;
--    ALTER TABLE `TestQuestions` DROP CONSTRAINT `FK_TestsQuestions_AnswerId`;
--    ALTER TABLE `TestQuestionAnswers` DROP CONSTRAINT `FK_TestsQuestionsAnswers_QuestionId`;
--    ALTER TABLE `TestQuestions` DROP CONSTRAINT `FK_TestsQuestions_TestId`;
--    ALTER TABLE `employeetestapprovals` DROP CONSTRAINT `FK_employeetestapprovals_employee`;
--    ALTER TABLE `employeetestapprovals` DROP CONSTRAINT `FK_employeetestapprovals_test`;

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------
SET foreign_key_checks = 0;
    DROP TABLE IF EXISTS `Documents`;
    DROP TABLE IF EXISTS `EmployeeAvailabilities`;
    DROP TABLE IF EXISTS `Employees`;
    DROP TABLE IF EXISTS `EmployeeSchedules`;
    DROP TABLE IF EXISTS `EmployeeTestResults`;
    DROP TABLE IF EXISTS `JobApplicants`;
    DROP TABLE IF EXISTS `TestQuestionAnswers`;
    DROP TABLE IF EXISTS `TestQuestions`;
    DROP TABLE IF EXISTS `Tests`;
    DROP TABLE IF EXISTS `employeetestapprovals`;
SET foreign_key_checks = 1;

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Documents'

CREATE TABLE `Documents` (
    `Id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `Name` longtext  NOT NULL,
    `Data` varbinary(100)  NOT NULL,
    `FileExtension` longtext  NOT NULL
);

-- Creating table 'EmployeeAvailabilities'

CREATE TABLE `EmployeeAvailabilities` (
    `Id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `EmployeeId` int  NOT NULL,
    `From` int  NOT NULL,
    `To` int  NOT NULL,
    `Day` longtext  NOT NULL
);

-- Creating table 'Employees'

CREATE TABLE `Employees` (
    `Id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `FirstName` longtext  NOT NULL,
    `MiddleName` longtext  NOT NULL,
    `LastName` longtext  NOT NULL,
    `PhoneNumber` bigint  NOT NULL,
    `EmailAddress` longtext  NULL,
    `Address` longtext  NOT NULL,
    `City` longtext  NOT NULL,
    `State` longtext  NOT NULL,
    `Zip` int  NOT NULL,
    `BirthDate` datetime  NOT NULL,
    `Username` longtext  NOT NULL,
    `Password` longtext  NOT NULL,
    `Role` longtext  NOT NULL
);

-- Creating table 'EmployeeSchedules'

CREATE TABLE `EmployeeSchedules` (
    `Id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `EmployeeId` int  NOT NULL,
    `StartDateTime` datetime  NOT NULL,
    `EndDateTime` datetime  NOT NULL
);

-- Creating table 'EmployeeTestResults'

CREATE TABLE `EmployeeTestResults` (
    `EmployeeId` int  NOT NULL,
    `QuestionId` int  NOT NULL,
    `AnswerId` int  NOT NULL
);

-- Creating table 'JobApplicants'

CREATE TABLE `JobApplicants` (
    `Id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `FirstName` longtext  NULL,
    `MiddleName` longtext  NULL,
    `LastName` longtext  NULL,
    `DateApplied` datetime  NULL,
    `Telephone` longtext  NULL,
    `SSN` longtext  NULL,
    `Address` longtext  NULL,
    `City` longtext  NULL,
    `State` longtext  NULL,
    `Zip` longtext  NULL,
    `Birthdate` longtext  NULL,
    `PreviousPennStationLocation` longtext  NULL,
    `PreviousPennStationStartDate` longtext  NULL,
    `PreviousPennStationEndDate` longtext  NULL,
    `HasFelony` longtext  NULL,
    `HasNonTrafficConviction` longtext  NULL,
    `ConvictionDescription` longtext  NULL,
    `IsAvailableDays` bool  NULL,
    `IsAvailableNights` bool  NULL,
    `IsAvailableFullTime` bool  NULL,
    `IsAvailablePartTime` bool  NULL,
    `PossibleStartDate` longtext  NOT NULL,
    `PreviousPennStationExperience` longtext  NOT NULL,
    `DatesWorked` longtext  NOT NULL,
    `FromAvailbilityMonday` longtext  NOT NULL,
    `FromAvailbilityTuesday` longtext  NOT NULL,
    `FromAvailbilityWednesday` longtext  NOT NULL,
    `FromAvailbilityThursday` longtext  NOT NULL,
    `FromAvailbilityFriday` longtext  NOT NULL,
    `FromAvailbilitySaturday` longtext  NOT NULL,
    `FromAvailbilitySunday` longtext  NOT NULL,
    `ToAvailbilityMonday` longtext  NOT NULL,
    `ToAvailbilityTuesday` longtext  NOT NULL,
    `ToAvailbilityWednesday` longtext  NOT NULL,
    `ToAvailbilityThursday` longtext  NOT NULL,
    `ToAvailbilityFriday` longtext  NOT NULL,
    `ToAvailbilitySaturday` longtext  NOT NULL,
    `ToAvailbilitySunday` longtext  NOT NULL,
    `HighSchoolNameLocation` longtext  NOT NULL,
    `HighSchoolCourseOfStudy` longtext  NOT NULL,
    `HighSchoolYearsComplete` longtext  NOT NULL,
    `CollegeNameLocation` longtext  NOT NULL,
    `CollegeCourseOfStudy` longtext  NOT NULL,
    `CollegeYearsComplete` longtext  NOT NULL,
    `TwoMostRecentJobsCompany1` longtext  NOT NULL,
    `OtherCourseOfStudy` longtext  NOT NULL,
    `OtherYearsComplete` longtext  NOT NULL,
    `TwoMostRecentJobsLocation1` longtext  NOT NULL,
    `TwoMostRecentJobsPhone1` longtext  NOT NULL,
    `TwoMostRecentJobsSupervisor1` longtext  NOT NULL,
    `TwoMostRecentJobsJobDescription1` longtext  NOT NULL,
    `TwoMostRecentJobsWage1` longtext  NOT NULL,
    `TwoMostRecentJobsReasonForLeaving1` longtext  NOT NULL,
    `TwoMostRecentJobsLocation2` longtext  NOT NULL,
    `TwoMostRecentJobsPhone2` longtext  NOT NULL,
    `TwoMostRecentJobsSupervisor2` longtext  NOT NULL,
    `TwoMostRecentJobsJobDescription2` longtext  NOT NULL,
    `TwoMostRecentJobsWage2` longtext  NOT NULL,
    `TwoMostRecentJobsReasonForLeaving2` longtext  NOT NULL,
    `Reference1Name` longtext  NOT NULL,
    `Reference1Address` longtext  NOT NULL,
    `Reference1Phone` bigint  NOT NULL,
    `Reference2Name` longtext  NOT NULL,
    `Reference2Address` longtext  NOT NULL,
    `Reference2Phone` bigint  NOT NULL,
    `Reference3Name` longtext  NOT NULL,
    `Reference3Address` longtext  NOT NULL,
    `Reference3Phone` bigint  NOT NULL,
    `TwoMostRecentJobsCompany2` longtext  NOT NULL,
    `Age` longtext  NOT NULL
);

-- Creating table 'TestQuestionAnswers'

CREATE TABLE `TestQuestionAnswers` (
    `Id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `QuestionId` int  NOT NULL,
    `Text` longtext  NOT NULL,
    `Number` int  NOT NULL
);

-- Creating table 'TestQuestions'

CREATE TABLE `TestQuestions` (
    `Id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `TestId` int  NOT NULL,
    `Text` longtext  NOT NULL,
    `AnswerId` int  NULL,
    `Number` int  NOT NULL
);

-- Creating table 'Tests'

CREATE TABLE `Tests` (
    `Id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `Name` longtext  NOT NULL,
    `Description` longtext  NOT NULL,
    `SkillId` int  NOT NULL
);

-- Creating table 'employeetestapprovals'

CREATE TABLE `employeetestapprovals` (
    `ApprovedEmployees_Id` int  NOT NULL,
    `ApprovedTests_Id` int  NOT NULL
);



-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on `EmployeeId`, `QuestionId`, `AnswerId` in table 'EmployeeTestResults'

ALTER TABLE `EmployeeTestResults`
ADD CONSTRAINT `PK_EmployeeTestResults`
    PRIMARY KEY (`EmployeeId`, `QuestionId`, `AnswerId` );

-- Creating primary key on `ApprovedEmployees_Id`, `ApprovedTests_Id` in table 'employeetestapprovals'

ALTER TABLE `employeetestapprovals`
ADD CONSTRAINT `PK_employeetestapprovals`
    PRIMARY KEY (`ApprovedEmployees_Id`, `ApprovedTests_Id` );



-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on `EmployeeId` in table 'EmployeeAvailabilities'

ALTER TABLE `EmployeeAvailabilities`
ADD CONSTRAINT `FK_EmployeeAvailability_EmployeeId`
    FOREIGN KEY (`EmployeeId`)
    REFERENCES `Employees`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeAvailability_EmployeeId'

CREATE INDEX `IX_FK_EmployeeAvailability_EmployeeId` 
    ON `EmployeeAvailabilities`
    (`EmployeeId`);

-- Creating foreign key on `EmployeeId` in table 'EmployeeSchedules'

ALTER TABLE `EmployeeSchedules`
ADD CONSTRAINT `FK_EmployeeSchedules_EmployeeId`
    FOREIGN KEY (`EmployeeId`)
    REFERENCES `Employees`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeSchedules_EmployeeId'

CREATE INDEX `IX_FK_EmployeeSchedules_EmployeeId` 
    ON `EmployeeSchedules`
    (`EmployeeId`);

-- Creating foreign key on `EmployeeId` in table 'EmployeeTestResults'

ALTER TABLE `EmployeeTestResults`
ADD CONSTRAINT `FK_EmployeeTestResults_EmplyeeId`
    FOREIGN KEY (`EmployeeId`)
    REFERENCES `Employees`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating foreign key on `AnswerId` in table 'EmployeeTestResults'

ALTER TABLE `EmployeeTestResults`
ADD CONSTRAINT `FK_EmployeeTestResults_AnswerId`
    FOREIGN KEY (`AnswerId`)
    REFERENCES `TestQuestionAnswers`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeTestResults_AnswerId'

CREATE INDEX `IX_FK_EmployeeTestResults_AnswerId` 
    ON `EmployeeTestResults`
    (`AnswerId`);

-- Creating foreign key on `QuestionId` in table 'EmployeeTestResults'

ALTER TABLE `EmployeeTestResults`
ADD CONSTRAINT `FK_EmployeeTestResults_QuestionId`
    FOREIGN KEY (`QuestionId`)
    REFERENCES `TestQuestions`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeTestResults_QuestionId'

CREATE INDEX `IX_FK_EmployeeTestResults_QuestionId` 
    ON `EmployeeTestResults`
    (`QuestionId`);

-- Creating foreign key on `AnswerId` in table 'TestQuestions'

ALTER TABLE `TestQuestions`
ADD CONSTRAINT `FK_TestsQuestions_AnswerId`
    FOREIGN KEY (`AnswerId`)
    REFERENCES `TestQuestionAnswers`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TestsQuestions_AnswerId'

CREATE INDEX `IX_FK_TestsQuestions_AnswerId` 
    ON `TestQuestions`
    (`AnswerId`);

-- Creating foreign key on `QuestionId` in table 'TestQuestionAnswers'

ALTER TABLE `TestQuestionAnswers`
ADD CONSTRAINT `FK_TestsQuestionsAnswers_QuestionId`
    FOREIGN KEY (`QuestionId`)
    REFERENCES `TestQuestions`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TestsQuestionsAnswers_QuestionId'

CREATE INDEX `IX_FK_TestsQuestionsAnswers_QuestionId` 
    ON `TestQuestionAnswers`
    (`QuestionId`);

-- Creating foreign key on `TestId` in table 'TestQuestions'

ALTER TABLE `TestQuestions`
ADD CONSTRAINT `FK_TestsQuestions_TestId`
    FOREIGN KEY (`TestId`)
    REFERENCES `Tests`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TestsQuestions_TestId'

CREATE INDEX `IX_FK_TestsQuestions_TestId` 
    ON `TestQuestions`
    (`TestId`);

-- Creating foreign key on `ApprovedEmployees_Id` in table 'employeetestapprovals'

ALTER TABLE `employeetestapprovals`
ADD CONSTRAINT `FK_employeetestapprovals_employee`
    FOREIGN KEY (`ApprovedEmployees_Id`)
    REFERENCES `Employees`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating foreign key on `ApprovedTests_Id` in table 'employeetestapprovals'

ALTER TABLE `employeetestapprovals`
ADD CONSTRAINT `FK_employeetestapprovals_test`
    FOREIGN KEY (`ApprovedTests_Id`)
    REFERENCES `Tests`
        (`Id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_employeetestapprovals_test'

CREATE INDEX `IX_FK_employeetestapprovals_test` 
    ON `employeetestapprovals`
    (`ApprovedTests_Id`);

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------
